package cn.edu.suda.ada.database.parser;

import cn.edu.suda.ada.database.objects.Column;
import cn.edu.suda.ada.database.objects.Key;
import cn.edu.suda.ada.database.objects.Key.Method;
import cn.edu.suda.ada.database.objects.Key.Type;
import cn.edu.suda.ada.database.objects.Table;
import cn.edu.suda.ada.database.objects.Table.Charset;
import cn.edu.suda.ada.database.objects.Table.Engine;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * MySql解析
 * 
 * @author JiangJun
 *
 */
public class MysqlDbParser extends DbParser {
	/**
	 * 获得替代字符串
	 *
	 * @param option
	 * @return
     */
	public String getReplacement(OPTIONS option) {
		String replacement = option.toString();
		switch(option) {
			case ORDER :
			case GROUP :
				replacement += " BY";
				break;
			case TABLE:
			case FIELD:
			case DATA:
				replacement = "";
			default:
		}
		replacement = " " + replacement + " ";
		return replacement;
	}

	@Override
	public String createTable(Table table) {
		if (null == table) {
			return null;
		}

		String name = table.getName();
		Set<Column<?>> columns = table.getColumns();
		Set<Key> keys = table.getKeys();
		Engine engine = table.getEngine();
		Charset charset = table.getCharset();
		String comment = table.getComment();

		if (null == name || name.isEmpty()) {
			name = "auto_created_table";
		}
		List<String> colsSqlList = new ArrayList<String>();
		if (columns.isEmpty()) {
			return null;
		}
		for (Column<?> col : columns) {
			colsSqlList.add(createColumn(col));
		}
		if (!keys.isEmpty()) {
			for (Key key : keys) {
				colsSqlList.add(createKey(key));
			}
		}

		return String.format("CREATE TABLE `%s` (%s) ENGINE=%s DEFAULT CHARSET=%s COMMENT='%s';",
						name, StringUtils.join(colsSqlList.toArray(), ','),
						engine, charset, comment);
	}

	@Override
	public String createColumn(Column<?> column) {
		if (null == column) {
			return null;
		}

		String name = column.getName();
		String type = column.getType().toString();
		Integer length = column.getLength();
		Boolean empty = column.isEmpty();
		String extra = column.getExtra();

		String comment = column.getComment();
		if (null == name || name.isEmpty()) {
			return null;
		}
		if (null == type || type.isEmpty()) {
			return null;
		}
		if (null != length && length>0) {
			type = type + "("+length+")";
		}
		String notNull = "";
		String defaultValue = "";
		if (null != empty && !empty) {
			notNull = "NOT NULL";
		}
		if (null != column.getDefaultVal()) {
			defaultValue = "DEFAULT '" + String.valueOf(column.getDefaultVal()) + "'";
		}
		if (null == comment) {
			comment = "";
		}
		if (null == extra) {
			extra = "";
		}
		return String.format("`%s` %s %s %s %s COMMENT '%s'", name, type, notNull, extra, defaultValue, comment);
	}

	@Override
	public String createKey(Key key) {
		if (null == key) {
			return null;
		}

		Set<String> columns = key.getColumns();
		String name = key.getName();
		Method method = key.getMethod();
		Type type = key.getType();

		if (columns.isEmpty()) {
			return null;
		}
		// 索引列
		List<String> keys = new ArrayList<String>();
		for (String col : columns) {
			keys.add("`" + col + "`");
			// 取最后一个作为索引名
			if (null == name) {
				name =  col;
			}
		}

		String usingMethod = "";
		if (null != method) {
			usingMethod = "USING " + method.toString();
		}
		return String.format("%s `%s` (%s) %s", type, name,
				StringUtils.join(keys.toArray(), ','), usingMethod);

	}

	@Override
	public String dropTable(String tableName) {
		if (null == tableName) {
			return null;
		}
		return String.format("DROP TABLE IF EXISTS `%s`", tableName);
	}

	@Override
	public String exist(String tableName) {
		if (null == tableName) {
			return null;
		}
		return String.format("SELECT COUNT(*) as count from information_schema.TABLES where table_name='%s'", tableName);
	}
	
	@Override
	public String showTable(String tableName, String databaseName) {
		if (null == tableName || null == databaseName) {
			return null;
		}
		return String.format("SHOW TABLE STATUS FROM %s WHERE NAME='%s'", databaseName, tableName);
	}
	
	@Override
	public Table mapToTable(Map<String, Object> map) {
		if (null == map || map.isEmpty()){
			return null;
		}
		String name = map.get("TABLE_NAME").toString();
		String engine = map.get("ENGINE").toString();
//		String charset = map.get("table_collation");
		Table table = new Table();
		table.setName(name);
		table.setEngine(Engine.getEngine(engine));
		return table;
	}
	
	@Override
	public String showColumns(String tableName){
		if (null == tableName) {
			return null;
		}
		return String.format("SHOW COLUMNS FROM %s", tableName);
	}
	
	@Override
	public Column<?> mapToColumn(Map<String, Object> map){
		if (null == map || map.isEmpty()){
			return null;
		}
		String name = map.get("COLUMN_NAME").toString();
		String type = map.get("COLUMN_TYPE").toString();
		String def = null == map.get("COLUMN_DEFAULT") ? null : map.get("COLUMN_DEFAULT").toString();
		Boolean empty = null != map.get("IS_NULLABLE") && !map.get("IS_NULLABLE").toString().equals("NO");
		String extra = null == map.get("EXTRA") ? null : map.get("EXTRA").toString();
		Column<?> column = Column.getColumn(name, type, def);
		if (null != column) {
			column.setEmpty(empty);
			column.setExtra(extra);
		}
		return column;
	}
	
	@Override
	public String showKeys(String tableName) {
		if (null == tableName) {
			return null;
		}
		return String.format("SHOW KEYS FROM %s", tableName);
	}

	@Override
	public Key mapToKey(Map<String, Object> map) {
		if (null == map || map.isEmpty()){
			return null;
		}
		String column = map.get("COLUMN_NAME").toString();
		String name = map.get("INDEX_NAME").toString();
		String nonUnique = map.get("NON_UNIQUE").toString();
		String method = map.get("INDEX_TYPE").toString();
		Key key = new Key();
		key.setName(name);
		key.setType(Type.getType(name, nonUnique));
		key.setMethod(Method.getMethod(method));
		key.addColumn(column);
		return key;
	}
	
	@Override
	public String alterColumn(String tableName, Column<?> oldColumn, Column<?> newColumn){
		if (null == tableName || null == oldColumn || null == newColumn) {
			return null;
		}
		return String.format("ALTER TABLE %s CHANGE COLUMN `%s` %s", tableName, oldColumn.getName(), createColumn(newColumn));
	}

	@Override
	public String dropColumn(String tableName,String columnName){
		if (null == tableName || null == columnName) {
			return null;
		}
		return String.format("ALTER TABLE %s DROP COLUMN `%s`", tableName, columnName);
	}
	
	@Override
	public String addColumn(String tableName,Column<?> column){
		if (null == tableName || null == column) {
			return null;
		}
		return String.format("ALTER TABLE %s ADD COLUMN %s", tableName, createColumn(column));
	}
	
	@Override
	public String copyTableStructure(String tableName, String newTableName){
		if (null == tableName || null == newTableName) {
			return null;
		}
		return String.format("CREATE TABLE `%s` LIKE `%s`", newTableName, tableName);
	}
	
	@Override
	public String copyTable(String tableName, String newTableName){
		if (null == tableName || null == newTableName) {
			return null;
		}
		return String.format("INSERT INTO `%s` (SELECT * FROM `%s`);", newTableName, tableName);
	}
	
	@Override
	protected String parserSql(SQLTemplate sqlTemplate, Map<OPTIONS, Object> options) {
		sql = sqlTemplate.toString();
		for (OPTIONS option: options.keySet()) {
			String replace = getReplacement(option);
			Object object = options.get(option);
			if (null != object) {
				String value = object.toString();
				value = (null != value && !value.isEmpty()) ? replace + ((option.equals(OPTIONS.DISTINCT) ? '`' + value + '`' : value)) + " " : "";
				sql =  sql.replace("%" + option + "%", value);
			}
		}
		for (OPTIONS option: OPTIONS.values()) {
			sql =  sql.replace("%" + option + "%", "");
		}
		sql = sql.replaceAll("\\s+", " ");
		return sql;
	}
	
	private void where(Map<OPTIONS, Object> options) {
		Object obj = options.get(OPTIONS.WHERE);
		if (obj instanceof Map) {
			@SuppressWarnings("unchecked")
			Map<String, Object> where = (Map<String, Object>) obj;
			if (!where.isEmpty()) {
				Object whereString = (String) where.get(WHERE_STRING);
				where.put(WHERE_STRING, null);
				List<String> whereList = new ArrayList<String>(where.size());
				if (null != whereString) {
					whereList.add(whereString.toString());
				}
				for (String key : where.keySet()) {
					Object value = where.get(key);
					if (null != value) {
						value = value.toString().replace("'", "\\'");
						whereList.add(String.format("%s = '%s'", key, value));
					}
				}
				options.put(OPTIONS.WHERE, StringUtils.join(whereList, " and "));
			}
		}
	}
	
	@Override
	public String select(Map<OPTIONS, Object> options) {
		// 一旦设置了DISTINCT，则FIELD失效
		if (null == options.get(OPTIONS.FIELD) && null == options.get(OPTIONS.DISTINCT)) {
			options.put(OPTIONS.FIELD, "*");
		}
		this.where(options);
		return parserSql(SQLTemplate.SELECT_SQL, options);
	}

	@Override
	public String count(Map<OPTIONS, Object> options, String field) {
		Object distinct = options.get(OPTIONS.DISTINCT);
		if (null == field || field.isEmpty() || "*".equals(field)) {
			Object tmp = options.get(OPTIONS.FIELD);
			if (null != tmp) {
				field = "`" + tmp.toString() + "`";
			} else {
				field = "*";
			}
		}
		if (null != distinct) {
			field = "DISTINCT(`" + distinct.toString() + "`)";
			options.remove(OPTIONS.DISTINCT);
		}
		field = " COUNT(" + field + ") ";
		options.put(OPTIONS.FIELD, field);
		options.put(OPTIONS.LIMIT, String.valueOf(1));
		return select(options);
	}

	@Override
	public String delete(Map<OPTIONS, Object> options) {
		this.where(options);
		return parserSql(SQLTemplate.DELETE_SQL, options);
	}

	private void insertData(Map<OPTIONS, Object> options) {
		Object obj = options.get(OPTIONS.DATA);
		if (obj instanceof Map) {
			@SuppressWarnings("unchecked")
			Map<String, Object> data = (Map<String, Object>) obj;
			if (!data.isEmpty()) {
				List<String> fields = new ArrayList<String>();
				List<String> values = new ArrayList<String>();
				for(String field : data.keySet()){
					fields.add("`" + field + "`");
					Object val = data.get(field);
					String value = "null";
					if (null != val) {
						value = val.toString().replace("'", "\\'");
						value = "'" + value + "'";
					}
					values.add(value);
				}
				options.put(OPTIONS.FIELD, StringUtils.join(fields, ','));
				options.put(OPTIONS.DATA, StringUtils.join(values, ','));
			}
		}
	}
	
	@Override
	public String insert(Map<OPTIONS, Object> options) {
		this.insertData(options);
		
		this.where(options);
		return parserSql(SQLTemplate.INSERT_SQL, options);
	}

	@Override
	public String insert(Map<OPTIONS, Object> options, Map<String, Object> data) {
		if (null == data || data.isEmpty()){
			return null;
		}
		options.put(OPTIONS.DATA, data);
		return this.insert(options);
	}

	private void updateData(Map<OPTIONS, Object> options) {
		Object obj = options.get(OPTIONS.DATA);
		if (obj instanceof Map) {
			@SuppressWarnings("unchecked")
			Map<String, Object> data = (Map<String, Object>) obj;
			if (!data.isEmpty()) {
				List<String> values = new ArrayList<String>();
				for(String field : data.keySet()){
					Object val = data.get(field);
					String value = "null";
					if (null != val) {
						value = val.toString().replace("'", "\\'");
						value = "'" + value + "'";
					}
					values.add(String.format("`%s` = %s", field, value));
				}
				
				options.put(OPTIONS.DATA, StringUtils.join(values.toArray(), ','));
			}
		}
	}
	
	
	@Override
	public String update(Map<OPTIONS, Object> options) {
		this.updateData(options);
		
		this.where(options);
		return parserSql(SQLTemplate.UPDATE_SQL, options);
	}

	@Override
	public String update(Map<OPTIONS, Object> options, Map<String, Object> data) {
		if (null == data || data.isEmpty()){
			return null;
		}
		options.put(OPTIONS.DATA, data);
		return this.update(options);
	}

}
